import pymysql
import xlwt
# 数据库操作
config = {
    "host": "1.117.167.225",
    "user": "root",
    "password": "Aa1234zxcv",
    "database": "qc_live",
    "charset": "utf8"
}


# 查询表里所有数据
def find_all():
    conn = pymysql.connect(**config)
    time = '2023-02-02'
    time1 = '2023-02-03'
    cursor = conn.cursor()
    tab_list = []
    query = ('select * from t_lucky_gift_statistics where time >= "'+str(time)+'" and time<"'+str(time1)+'" ORDER BY input DESC')
    cursor.execute(query)
    result = cursor.fetchall()
    for (item) in result:
        # print(item)
        my_map = {'uid': item[1], 'input': item[2], 'output': item[3]}

        # 查询该用户的充值 昵称
        # select SUM(coin),nick from t_wallet_recharge_record where uid = 104773 and pay_type=1 and order_time >= '2023-02-02' and order_time<'2023-02-03'
        query1 = ('select SUM(coin),nick from t_wallet_recharge_record where uid = '+str(item[1])+' and is_pay=1 and order_time >= "' + str(
            time) + '" and order_time<"' + str(time1) + '"')
        cursor.execute(query1)
        result1 = cursor.fetchall()
        for (item1) in result1:
            # print(item1)
            my_map['nick'] = item1[1]
            my_map['recharge'] = item1[0]

        # 查询背包余额
        query2 = 'select SUM(num*coin) from t_user_pack as a inner join t_gift_config as b on a.prop_id=b.id where a.uid = '+str(item[1])+' and a.time >= "'+str(time)+'" and a.time<'+str(time1)+' and a.num>0 and a.type =5'
        cursor.execute(query2)
        result2 = cursor.fetchall()
        bg_coin = 0
        for (item2) in result2:
            try:
                bg_coin += item2[0]
            except:
                print('没有背包')
        my_map['bg_coin'] = bg_coin

        # 查询用户账户余额
        query3 = 'select * from t_wallet_user_account where uid = '+str(item[1])
        cursor.execute(query3)
        result3 = cursor.fetchall()
        for (item3) in result3:
            my_map['coin'] = item3[1]
            my_map['cost'] = item3[2]

        tab_list.append(my_map)

    cursor.close()

    # 生成报表
    work_book = xlwt.Workbook(encoding='utf-8')
    sheet = work_book.add_sheet('sheet表名')
    sheet.write(0, 0, '用户ID')
    sheet.write(0, 1, '昵称')
    sheet.write(0, 2, '幸运礼物投入')
    sheet.write(0, 3, '幸运礼物产出')
    sheet.write(0, 4, '成本(元)')
    sheet.write(0, 5, '上榜(元)')
    sheet.write(0, 6, '今日盈亏(元)')
    sheet.write(0, 7, '今日充值(元)')
    sheet.write(0, 8, '背包余额')
    sheet.write(0, 9, '鹿角')
    sheet.write(0, 10, '鹿茸')
    sheet.write(0, 11, '产出率')

    yl_num = 0
    yl_coin = 0
    ks_num = 0
    ks_coin = 0
    for index in range(len(tab_list)):
        sheet.write(index + 1, 0, tab_list[index]['uid'])
        sheet.write(index + 1, 1, tab_list[index]['nick'])
        sheet.write(index + 1, 2, tab_list[index]['input'])
        sheet.write(index + 1, 3, tab_list[index]['output'])
        sheet.write(index + 1, 4, (tab_list[index]['input']-tab_list[index]['output'])/1000)
        sheet.write(index + 1, 5, tab_list[index]['input']/10000)
        je = tab_list[index]['input']/10000 - ((tab_list[index]['input']-tab_list[index]['output'])/1000)
        if je > 0:
            yl_num = yl_num + 1
            yl_coin += je
        else:
            ks_num = ks_num + 1
            ks_coin -= je
        sheet.write(index + 1, 6, je)
        sheet.write(index + 1, 7, 0 if tab_list[index]['recharge'] == None else tab_list[index]['recharge']/1000)
        sheet.write(index + 1, 8, tab_list[index]['bg_coin'])
        sheet.write(index + 1, 9, tab_list[index]['coin'])
        sheet.write(index + 1, 10, tab_list[index]['cost'])
        sheet.write(index + 1, 11, round(tab_list[index]['output']/tab_list[index]['input'], 2))
    # 今日总充值金额
    recharge = 0
    for it1 in tab_list:
        if it1['recharge'] != None:
            recharge += it1['recharge']
    sheet.write(0, 14, '今日总充值')
    sheet.write(1, 14, recharge)

    # 平台总的投入产出 盈利情况
    sum_input = 0
    sum_output = 0
    for it2 in tab_list:
        if it2['input'] != None:
            sum_input += it2['input']
        if it2['output'] != None:
            sum_output += it2['output']
    sheet.write(0, 16, '今日总投入')
    sheet.write(0, 17, '今日总产出')
    sheet.write(0, 18, '平台盈亏')
    sheet.write(1, 16, sum_input)
    sheet.write(1, 17, sum_output)
    sheet.write(1, 18, sum_input/10000 - ((sum_input-sum_output)/1000))

    # 平台所有人的盈亏情况人数情况
    sheet.write(3, 16, '盈利人数')
    sheet.write(3, 17, '盈利金额')
    sheet.write(3, 18, '亏损人数')
    sheet.write(3, 19, '亏损金额')
    sheet.write(4, 16, yl_num)
    sheet.write(4, 17, yl_coin)
    sheet.write(4, 18, ks_num)
    sheet.write(4, 19, ks_coin)

    work_book.save('C:\\Users\\Administrator\\Documents\\xiaolu\\'+str(time)+'_幸运礼物投入产出.xls')
    conn.close()


if __name__ == '__main__':
    # 幸运礼物平台整体情况
    find_all()
